#!/usr/bin/python
#coding:utf-8
#File Name    : monitor_auto_increment_state.py
#Author       : moshan
#Mail         : shan.mo01@mljr.com
#Created Time : 2019-10-21 10:46:03
#Function     : 监控美利金融环境自增列状态
#########################################################################
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email import encoders
import sys
import os
import pymysql
from datetime import *
#

#mysql_host_w = "dbmp."
mysql_host_w = "10.8.104"
mysql_user_w = "user_dbmp"
mysql_password_w = ""
mysql_db_w = "dbmp_db"
mysql_port_w = 3308

mysql_user_rd = "dba_rd"
mysql_password_rd = ""

environment_info = "车环境"

g_usage_rate = "50"   #使用率超过这个值就告警


def connect_mysql(mysql_host, mysql_user, mysql_password, mysql_db, mysql_port, sql_info, sql_type):
        db = pymysql.connect(mysql_host, mysql_user, mysql_password, mysql_db, mysql_port, charset='utf8')
        cursor = db.cursor()
        if sql_type == 'dml' :
            try:
                cursor.execute(sql_info)
                db.commit()
            except Exception as e:
                print t_today + str(e.args) + " ---------- SQL   INFO : " + insert_sql
                print insert_sql
        elif sql_type == 'select' :
            cursor.execute(sql_info)
            data = cursor.fetchall()
            return data
        else :
            sys.exit()


def get_res(mysql_host, mysql_user, mysql_password, mysql_db, mysql_port):
        t_today = datetime.now()
        t_today = str(t_today)[0:19]
        sql = """
SELECT 
    id,
    ip_port_id,
    table_schema,
    table_name,
    column_name,
    column_type,
    PRIMARY_KEY,
    max_value,
    auto_increment,
    usage_rate
FROM
    (SELECT 
        0 'id',
            "ifnull((SELECT id FROM dbmp_db.backup_project WHERE host = '""" + str(mysql_host) + """' AND port = """ + str(mysql_port) + """),0)" ip_port_id,
            s.table_schema,
            s.table_name,
            s.column_name,
            s.column_type,
            IF(c.column_key = 'PRI', 'YES', 'NO') AS 'PRIMARY_KEY',
            s.max_value,
            s.auto_increment,
            ROUND(s.auto_increment_ratio*100) Usage_Rate
    FROM
        (SELECT 
        `information_schema`.`columns`.`TABLE_SCHEMA` AS `table_schema`,
            `information_schema`.`columns`.`TABLE_NAME` AS `table_name`,
            `information_schema`.`columns`.`COLUMN_NAME` AS `column_name`,
            `information_schema`.`columns`.`DATA_TYPE` AS `data_type`,
            `information_schema`.`columns`.`COLUMN_TYPE` AS `column_type`,
            (LOCATE('unsigned', `information_schema`.`columns`.`COLUMN_TYPE`) = 0) AS `is_signed`,
            (LOCATE('unsigned', `information_schema`.`columns`.`COLUMN_TYPE`) > 0) AS `is_unsigned`,
            ((CASE `information_schema`.`columns`.`DATA_TYPE`
                WHEN 'tinyint' THEN 255
                WHEN 'smallint' THEN 65535
                WHEN 'mediumint' THEN 16777215
                WHEN 'int' THEN 4294967295
                WHEN 'bigint' THEN 18446744073709551615
            END) >> IF((LOCATE('unsigned', `information_schema`.`columns`.`COLUMN_TYPE`) > 0), 0, 1)) AS `max_value`,
            `information_schema`.`tables`.`AUTO_INCREMENT` AS `auto_increment`,
            (`information_schema`.`tables`.`AUTO_INCREMENT` / ((CASE `information_schema`.`columns`.`DATA_TYPE`
                WHEN 'tinyint' THEN 255
                WHEN 'smallint' THEN 65535
                WHEN 'mediumint' THEN 16777215
                WHEN 'int' THEN 4294967295
                WHEN 'bigint' THEN 18446744073709551615
            END) >> IF((LOCATE('unsigned', `information_schema`.`columns`.`COLUMN_TYPE`) > 0), 0, 1))) AS `auto_increment_ratio`
    FROM
        (`information_schema`.`columns`
    JOIN `information_schema`.`tables` ON (((`information_schema`.`columns`.`TABLE_SCHEMA` = `information_schema`.`tables`.`TABLE_SCHEMA`)
        AND (`information_schema`.`columns`.`TABLE_NAME` = `information_schema`.`tables`.`TABLE_NAME`))))
    WHERE
        ((`information_schema`.`columns`.`TABLE_SCHEMA` NOT IN ('mysql' , 'sys', 'INFORMATION_SCHEMA', 'performance_schema'))
            AND (`information_schema`.`tables`.`TABLE_TYPE` = 'BASE TABLE')
            AND (`information_schema`.`columns`.`EXTRA` = 'auto_increment'))
    ORDER BY (`information_schema`.`tables`.`AUTO_INCREMENT` / ((CASE `information_schema`.`columns`.`DATA_TYPE`
        WHEN 'tinyint' THEN 255
        WHEN 'smallint' THEN 65535
        WHEN 'mediumint' THEN 16777215
        WHEN 'int' THEN 4294967295
        WHEN 'bigint' THEN 18446744073709551615
    END) >> IF((LOCATE('unsigned', `information_schema`.`columns`.`COLUMN_TYPE`) > 0), 0, 1))) DESC , ((CASE `information_schema`.`columns`.`DATA_TYPE`
        WHEN 'tinyint' THEN 255
        WHEN 'smallint' THEN 65535
        WHEN 'mediumint' THEN 16777215
        WHEN 'int' THEN 4294967295
        WHEN 'bigint' THEN 18446744073709551615
    END) >> IF((LOCATE('unsigned', `information_schema`.`columns`.`COLUMN_TYPE`) > 0), 0, 1))) s
    JOIN information_schema.tables t ON t.table_schema = s.table_schema
        AND t.table_name = s.table_name
    JOIN information_schema.columns c ON t.table_schema = c.table_schema
        AND t.table_name = c.table_name
        AND s.column_name = c.column_name
    ORDER BY s.auto_increment DESC) a
WHERE
    Usage_Rate >= 
""" + g_usage_rate + ";"
        data = connect_mysql(mysql_host, mysql_user, mysql_password, mysql_db, mysql_port, sql, 'select')
        return data


def write_res_to_mysql():
        t_today = datetime.now()
        t_today = str(t_today)[0:19]
        sql = "select host,port from backup_project"
        ip_list = connect_mysql(mysql_host_w, mysql_user_w, mysql_password_w, mysql_db_w, mysql_port_w, sql, 'select')
        for ip_port in ip_list:
            data = get_res(str(ip_port[0]), mysql_user_rd, mysql_password_rd, 'mysql', int(ip_port[1]))
            for row in data:
                insert_sql = "insert into t_monitor_auto_increment(id, ip_port_id, table_schema, table_name, column_name, column_type, primary_key_stat, max_auto_increment, auto_increment, usage_rate) select " + str(row[0]) + ", " + str(row[1]) + ", '" + str(row[2]) + "', '" + str(row[3]) + "', '" + str(row[4]) + "', '" + str(row[5]) + "', '" + str(row[6]) + "', '" + str(row[7]) + "', '" + str(row[8]) + "', '" + str(row[9]) + "';"
                insert_state = connect_mysql(mysql_host_w, mysql_user_w, mysql_password_w, mysql_db_w, mysql_port_w, insert_sql, 'dml')


def send_mail(to_list, sub):
        me = mail_user 
        msg = MIMEMultipart()
        msg['Subject'] = sub
        msg['From'] = me
        msg['To'] = ",".join(to_list)
        time_now = str(datetime.now())[0:19]
        table_info = []
        html = ""
        html_tmp = ""
        write_res_to_mysql()
        t_today = datetime.now()
        t_today = str(t_today)[0:10]
        sql = "select concat(bp.host,':', bp.port) ip_port, table_schema, table_name, column_name, column_type, primary_key_stat, max_auto_increment, auto_increment, usage_rate , create_time from t_monitor_auto_increment t join backup_project bp on bp.id = t.ip_port_id where create_time like '" + t_today + "%' and usage_rate >= " + g_usage_rate + " order by usage_rate desc"
        data = connect_mysql(mysql_host_w, mysql_user_w, mysql_password_w, mysql_db_w, mysql_port_w, sql, 'select')
        if len(data) < 1:
            #结果为空就直接退出
            sys.exit()
        for row in data:
            data_tmp = row
            table_info_tmp = str(data_tmp[0].encode('utf-8'))
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = str(data_tmp[1].encode('utf-8'))
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = str(data_tmp[2].encode('utf-8'))
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = str(data_tmp[3].encode('utf-8'))
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = str(data_tmp[4].encode('utf-8'))
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = str(data_tmp[5].encode('utf-8'))
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = str(data_tmp[6])
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = str(data_tmp[7])
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = int(str(data_tmp[8]))

            if table_info_tmp >= 80 :
                tmp_str = "<td style='background-color:#FF0000'><strong>" + str(table_info_tmp)  + "</strong></td>"
            elif table_info_tmp >= 70 :
                tmp_str = "<td style='background-color:#FFFF00'><strong>" + str(table_info_tmp)  + "</strong></td>"
            elif table_info_tmp >= 50 :
                tmp_str = "<td style='background-color:#0000FF'><strong>" + str(table_info_tmp)  + "</strong></td>"
            else :
                tmp_str = "<td><strong>" + str(table_info_tmp)  + "</strong></td>"
            table_info.append(tmp_str)
            table_info_tmp = str(data_tmp[9])
            tmp_str = "<td><strong>" + table_info_tmp + "</strong></td>"
            table_info.append(tmp_str)
            html_tmp += "<tr>" + table_info[0] + "\n" + table_info[1] + "\n" + table_info[2] + "\n" + table_info[3] + "\n" + table_info[4] + "\n" + table_info[5] + "\n" + table_info[6] + "\n" + table_info[7] + "\n" + table_info[8] + "\n" + table_info[9] + "\n" + "</tr>\n"
            table_info = []
	html += """
	<html>
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>自增列状态采集</title>
	<body>
	<div id="container">
	  <p><span style="color:red;"><strong>美利金融-""" + environment_info + """-自增列状态采集</strong></span></p>
	  <p>采集时间: """ + time_now + """</p>
	  <div id="content">
	   <table width="1000" border="2" bordercolor="black" cellspacing="2" style="font-family'宋体'; font-size50px;">
	  <tr>
	    <td><font color="#0000FF"><strong>IP地址:端口</strong></font></td>
	    <td><font color="#0000FF"><strong>业务库名称</strong></font></td>
	    <td><font color="#0000FF"><strong>业务表名称</strong></font></td>
	    <td><font color="#0000FF"><strong>字段名称</strong></font></td>
	    <td><font color="#0000FF"><strong>字段类型</strong></font></td>
	    <td><font color="#0000FF"><strong>是否是主键</strong></font></td>
	    <td><font color="#0000FF"><strong>自增列最大值</strong></font></td>
	    <td><font color="#0000FF"><strong>自增列当前值</strong></font></td>
	    <td><font color="#0000FF"><strong>自增列使用率</strong></font></td>
	    <td><font color="#0000FF"><strong>采集时间</strong></font></td>
	  </tr>""" + html_tmp + """
</table>
  </div>
</div>
</div>
</body>
</html>
        """
       #构造html
#构造html
        context = MIMEText(html,_subtype='html',_charset='utf-8')  #解决乱码
        msg.attach(context) 
        try:
                send_smtp = smtplib.SMTP()
                send_smtp.connect(mail_host)
                send_smtp.login(mail_user, mail_pass)
                send_smtp.sendmail(me, to_list, msg.as_string())
                send_smtp.close()
                return True
        except Exception, e:
                print str(e)[1]
                return False
if __name__ == '__main__':
   # 设置服务器名称、用户名、密码以及邮件后缀
    mail_host = 'smtp.exmail.qq.com'
    mail_user = 'notice@mljr.com'
    mail_pass = ''
    #mailto_lists = sys.argv[1]
    #mailto_list = mailto_lists.split(',')   #发送多人
    #sub= sys.argv[2]
    mailto_list = ['op-db@mljr.com',]
    #mailto_list = ['shan.mo01@mljr.com',]
    sub= "自增列状态采集-" + environment_info
    time_now = str(datetime.now())[0:19]
    if send_mail(mailto_list, sub):
            print "[", time_now, "] [ INFO ] Send mail succed!"
    else:
            print "Send mail failed!"
            print "[", time_now, "] [ ERROR ] Send mail failed!"
